package dejava.bolsadetrabajo.repository.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import dejava.bolsadetrabajo.repository.CurriculumRepository;
import dejava.bolsadetrabajo.repository.model.Curriculum;
import dejava.bolsadetrabajo.repository.model.Estudio;
import dejava.bolsadetrabajo.repository.model.Experiencia;
import dejava.bolsadetrabajo.repository.model.Habilidad;
import dejava.bolsadetrabajo.repository.model.Idioma;

public class CurriculumRepositoryJdbc implements CurriculumRepository {

	//@Override
	public void registrarCurriculum(int idPostulante, String foto, String cv, String ubicacion) throws SQLException  {

		String sql = "";
		sql += "INSERT INTO bolsatrabajo.curriculum";
		sql += "(idPostulante,foto,cv)";
		sql += "VALUES";
		sql += "(?,?,?,?)";

		Connection conn = null;
		PreparedStatement ps = null;

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306",
					"root", "123");

			ps = conn.prepareStatement(sql);
			ps.setInt(1, idPostulante);
			ps.setString(2, foto);
			ps.setString(3, cv);
			ps.setString(4,ubicacion);
			ps.executeUpdate();

		} catch (Exception except) {
			SQLException ex = new SQLException(
					"Error Establishing Connection: " +
					except.getMessage());
				throw ex;
		} finally {
			
			if (ps != null) {
				try {
					// Close to prevent database cursor exhaustion
					ps.close();
				} catch (SQLException ex) {
				}
			}
			if (conn != null) {
				try {
					// Close to prevent database connection exhaustion
					conn.close();
				} catch (SQLException ex) {
				}
			}
		}
	}

	//@Override
	public void registrarExperienciaLaboral(int idCurriculum, String organizacion,
			String industria, String posicion, String nivel,
			int nroPersonas, String tipoRelacion, String fechaInicio,
			String fechaTermino, String descripcion) throws SQLException {

		String sql = "";
		sql += "INSERT INTO bolsatrabajo.experiencia";
		sql += "(idCurriculum,organizacion,industria,posicion,";
		sql += "nivel,nroPersonas,tipoRelacion,fechaInicio,fechaTermino,descripcion)";
		sql += "VALUES";
		sql += "(?,?,?,?,?,?,?,?,?,?)";

		Connection conn = null;
		PreparedStatement ps = null;

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306",
					"root", "123");

			ps = conn.prepareStatement(sql);
			
			ps.setInt(1, idCurriculum);
			ps.setString(2,organizacion);
			ps.setString(3,industria);
			ps.setString(4,posicion);
			ps.setString(5,nivel);
			ps.setInt(6,nroPersonas);
			ps.setString(7,tipoRelacion);
			ps.setString(8,fechaInicio);
			ps.setString(9,fechaTermino);
			ps.setString(10,descripcion);

			ps.executeUpdate();

		} catch (Exception except) {
			SQLException ex = new SQLException(
					"Error Establishing Connection: " +
					except.getMessage());
				throw ex;
		} finally {
			
			if (ps != null) {
				try {
					// Close to prevent database cursor exhaustion
					ps.close();
				} catch (SQLException ex) {
				}
			}
			if (conn != null) {
				try {
					// Close to prevent database connection exhaustion
					conn.close();
				} catch (SQLException ex) {
				}
			}
		}
	}

	//@Override
	public void registrarEstudios(int idCurriculum, String nivelEducacion, String estado,
			String especialidad, String ciclosRestantes, String titulo, String institucion,
			String pais, String fechaInicio, String fechaFinal) throws SQLException {
		

		String sql = "";
		sql += "INSERT INTO bolsatrabajo.estudio";
		sql += "(idCurriculum,nivelEducacion,estado,especialidad,";
	    sql += "ciclosRestantes,titulo,institucion,pais,fechaInicio,fechaFinal)";
		sql += "VALUES";
		sql += "(?,?,?,?,?,?,?,?,?,?)";

		Connection conn = null;
		PreparedStatement ps = null;

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306",
					"root", "123");

			ps = conn.prepareStatement(sql);
			ps.setInt(1, idCurriculum);
			ps.setString(2,nivelEducacion);
			ps.setString(3,estado);
			ps.setString(4,especialidad);
			ps.setString(5,ciclosRestantes);
			ps.setString(6,titulo);
			ps.setString(7,institucion);
			ps.setString(8,pais);
			ps.setString(9,fechaInicio);
			ps.setString(10,fechaFinal);

			ps.executeUpdate();

		} catch (Exception except) {
			SQLException ex = new SQLException(
					"Error Establishing Connection: " +
					except.getMessage());
				throw ex;
		} finally {
			
			if (ps != null) {
				try {
					// Close to prevent database cursor exhaustion
					ps.close();
				} catch (SQLException ex) {
				}
			}
			if (conn != null) {
				try {
					// Close to prevent database connection exhaustion
					conn.close();
				} catch (SQLException ex) {
				}
			}
		}
		
	}

	//@Override
	public void registrarIdiomas(int idCurriculum, String idioma, String tipo,
			String dominioLectura, String dominioEscritura, String dominioConversacion) throws SQLException {
		String sql = "";
		sql += "INSERT INTO bolsatrabajo.idioma";
		sql += "(idCurriculum,idioma,tipo,dominioLectura,dominioEscritura,dominioConversacion)";
		sql += "VALUES";
		sql += "(?,?,?,?,?,?)";
		
		
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306",
					"root", "123");

			ps = conn.prepareStatement(sql);
			ps.setInt(1, idCurriculum);
			ps.setString(2,idioma);
			ps.setString(3,tipo);
			ps.setString(4,dominioLectura);
			ps.setString(5,dominioEscritura);
			ps.setString(6,dominioConversacion);
			ps.executeUpdate();

		} catch (Exception except) {
			SQLException ex = new SQLException(
					"Error Establishing Connection: " +
					except.getMessage());
				throw ex;
		} finally {
			
			if (ps != null) {
				try {
					// Close to prevent database cursor exhaustion
					ps.close();
				} catch (SQLException ex) {
				}
			}
			if (conn != null) {
				try {
					// Close to prevent database connection exhaustion
					conn.close();
				} catch (SQLException ex) {
				}
			}
		}

	}

	//@Override
	public void registrarHabilidad(int idCurriculum, String categoria) throws SQLException {
		String sql = "";
		sql += "INSERT INTO bolsatrabajo.habilidad";
		sql += "(idCurriculum,categoria)";
		sql += "VALUES";
		sql += "(?,?)";
		
		
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306",
					"root", "123");

			ps = conn.prepareStatement(sql);
			ps.setInt(1, idCurriculum);
			ps.setString(2,categoria);
			
			ps.executeUpdate();

		} catch (Exception except) {
			SQLException ex = new SQLException(
					"Error Establishing Connection: " +
					except.getMessage());
				throw ex;
		} finally {
			
			if (ps != null) {
				try {
					// Close to prevent database cursor exhaustion
					ps.close();
				} catch (SQLException ex) {
				}
			}
			if (conn != null) {
				try {
					// Close to prevent database connection exhaustion
					conn.close();
				} catch (SQLException ex) {
				}
			}
		}


	}

	//@Override
	public Curriculum buscarCurriculum(int idPostulante) throws SQLException {
		Curriculum c = new Curriculum();
		String sql = "select * from bolsatrabajo.curriculum where idpostulante='" + idPostulante + "'"  ;
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;	
		
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();			
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306",
			        "root", "123");

			
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery(sql);
			while (rs.next()){
			
				c.setIdCurriculum(rs.getInt("IdCurriculum"));
				c.setFoto(rs.getString("foto"));
				c.setCv(rs.getString("cv"));
			
				
				
				
				
			}		
		}
		catch (Exception except) {
			SQLException ex = new SQLException(
				"Error Establishing Connection: " +
				except.getMessage());
			throw ex;
		}finally{
			if (rs != null) {
				try {
					// Close to prevent database cursor exhaustion
					rs.close();
				} catch (SQLException ex) {
				}
			}
			if (ps != null) {
				try {
					// Close to prevent database cursor exhaustion
					ps.close();
				} catch (SQLException ex) {
				}
			}	
			if (conn != null) {
				try {
					// Close to prevent database connection exhaustion
					conn.close();
				} catch (SQLException ex) {
				}
			}			
		}
		return c;		
		
		
	}

	//@Override
	public List<Experiencia> obtenerExperiencia(int idCurriculum) throws SQLException {
		List<Experiencia> experiencia = new ArrayList <Experiencia>();
		String sql = "select * from bolsatrabajo.experiencia where idcurriculum='" + idCurriculum + "'"  ;
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;	
		
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();			
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306",
			        "root", "123");

			
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery(sql);
			while (rs.next()){
				Experiencia e= new Experiencia();
			
			    e.setIdExperiencia(rs.getInt("idExperiencia"));
				e.setIdCurriculum(rs.getInt("idCurriculum"));
				e.setNombre(rs.getString("organizacion"));
				e.setIndustria(rs.getString("industria"));
				e.setPosicion(rs.getString("posicion"));
				e.setNivel(rs.getString("nivel"));
				e.setNroPersonas(rs.getInt("nropersonas"));
				e.setTipoRelacion(rs.getString("tiporelacion"));
				e.setFechaInicio(rs.getString("fechainicio"));
				e.setFechaTermino(rs.getString("fechatermino"));
				e.setDescripcion(rs.getString("descripcion"));
				
				experiencia.add(e);			
			}		
		}
		catch (Exception except) {
			SQLException ex = new SQLException(
				"Error Establishing Connection: " +
				except.getMessage());
			throw ex;
		}finally{
			if (rs != null) {
				try {
					// Close to prevent database cursor exhaustion
					rs.close();
				} catch (SQLException ex) {
				}
			}
			if (ps != null) {
				try {
					// Close to prevent database cursor exhaustion
					ps.close();
				} catch (SQLException ex) {
				}
			}	
			if (conn != null) {
				try {
					// Close to prevent database connection exhaustion
					conn.close();
				} catch (SQLException ex) {
				}
			}			
		}
		return experiencia;		
	}

	//@Override
	public List<Estudio> obtenerEstudio(int idCurriculum) throws SQLException {
		 List<Estudio> estudio = new  ArrayList<Estudio>();
		String sql = "select * from bolsatrabajo.estudio where idcurriculum='" + idCurriculum + "'"  ;
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;	
		
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();			
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306",
			        "root", "123");

			
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery(sql);
			while (rs.next()){
				Estudio es = new Estudio();
				es.setIdEstudio(rs.getInt("idEstudio"));
				es.setIdCurriculum(rs.getInt("idCurriculum"));
				es.setNivelEducacion(rs.getString("nivelEducacion"));
				es.setEstado(rs.getString("estado"));
				es.setEspecialidad(rs.getString("especialidad"));
				es.setCiclosRestantes(rs.getString("ciclosRestantes"));
				es.setTitulo(rs.getString("titulo"));
				es.setInstitucion(rs.getString("institucion"));
				es.setPais(rs.getString("pais"));
				es.setFechaInicio(rs.getString("fechaInicio"));
				es.setFechaFinal(rs.getString("fechaFinal"));
				estudio.add(es);
			
			}		
		}
		catch (Exception except) {
			SQLException ex = new SQLException(
				"Error Establishing Connection: " +
				except.getMessage());
			throw ex;
		}finally{
			if (rs != null) {
				try {
					// Close to prevent database cursor exhaustion
					rs.close();
				} catch (SQLException ex) {
				}
			}
			if (ps != null) {
				try {
					// Close to prevent database cursor exhaustion
					ps.close();
				} catch (SQLException ex) {
				}
			}	
			if (conn != null) {
				try {
					// Close to prevent database connection exhaustion
					conn.close();
				} catch (SQLException ex) {
				}
			}			
		}
		return estudio;		
	}

	//@Override
	public List<Idioma> obtenerIdioma(int idCurriculum) throws SQLException {
		 List<Idioma> idioma=new  ArrayList<Idioma>();
		String sql = "select * from bolsatrabajo.idioma where idcurriculum='" + idCurriculum + "'" ;
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;	
		
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();			
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306",
			        "root", "123");

			
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery(sql);
			while (rs.next()){
				Idioma i = new Idioma();
				i.setIdIdioma(rs.getInt("idIdioma"));
				i.setIdCurriculum(rs.getInt("IdCurriculum"));
				i.setIdioma(rs.getString("idioma"));
				i.setTipo(rs.getString("tipo"));
				i.setDominioLectura(rs.getString("dominioLectura"));
				i.setDominioEscritura(rs.getString("dominioEscritura"));
				i.setDominioConversacion(rs.getString("dominioConversacion"));
			idioma.add(i);
			}		
		}
		catch (Exception except) {
			SQLException ex = new SQLException(
				"Error Establishing Connection: " +
				except.getMessage());
			throw ex;
		}finally{
			if (rs != null) {
				try {
					// Close to prevent database cursor exhaustion
					rs.close();
				} catch (SQLException ex) {
				}
			}
			if (ps != null) {
				try {
					// Close to prevent database cursor exhaustion
					ps.close();
				} catch (SQLException ex) {
				}
			}	
			if (conn != null) {
				try {
					// Close to prevent database connection exhaustion
					conn.close();
				} catch (SQLException ex) {
				}
			}			
		}
		return idioma;		
	}

	//@Override
	public List<Habilidad> obtenerHabilidad(int idCurriculum) throws SQLException {
		List<Habilidad> habilidad= new ArrayList<Habilidad>();
		String sql = "select * from bolsatrabajo.habilidad where idCurriculum='" + idCurriculum + "'"  ;
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;	
		
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();			
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306",
			        "root", "123");

			
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery(sql);
			while (rs.next()){
				Habilidad h = new Habilidad();
			    h.setIdHabilidad(rs.getInt("idHabilidad"));
				h.setIdCurriculum(rs.getInt("idCurriculum"));
				h.setCategoria(rs.getString("categoria"));

				habilidad.add(h);
			}		
		}
		catch (Exception except) {
			SQLException ex = new SQLException(
				"Error Establishing Connection: " +
				except.getMessage());
			throw ex;
		}finally{
			if (rs != null) {
				try {
					// Close to prevent database cursor exhaustion
					rs.close();
				} catch (SQLException ex) {
				}
			}
			if (ps != null) {
				try {
					// Close to prevent database cursor exhaustion
					ps.close();
				} catch (SQLException ex) {
				}
			}	
			if (conn != null) {
				try {
					// Close to prevent database connection exhaustion
					conn.close();
				} catch (SQLException ex) {
				}
			}			
		}
		return habilidad;		
	}

	

}
